package com.ce.pms.customer.dao.impl;

import java.util.List;


import com.ce.pms.base.dao.impl.PmsBaseDaoImpl;
import com.ce.pms.common.model.SysrefcodeDO;
import com.ce.pms.customer.dao.IHisResidentDao;
import com.ce.pms.customer.model.ResidentInHisDO;
import com.ce.pms.customer.vo.CustSearchVO;
import com.ce.util.BlankUtil;
import com.sitechasia.webx.core.support.Page;

public class HisResidentDaoImpl extends PmsBaseDaoImpl<ResidentInHisDO> implements IHisResidentDao {

	public List queryHisResidents() {
		return null;
	}
	/***
	 * Function: 居住历史查询方法
	 * @param custSearch 查询条件
	 * @param pageNO   查询第多少页
	 * @param pageSize 每页显示记录数
	 * @return page 查询结果集
	 * Create author:于国岗
	 * Create on:2008-6-6
	 * Edit author:
	 * Edit on:
	 * Why:
	 */
	public Page queryHisResidents(CustSearchVO vo, Integer pageNO, Integer pageSize) {
		//个人
		SysrefcodeDO individual = sysrefcodeDao.getSysrefCodesByCode("CS_PARTICIPANT_TYPE", "001");
		//企业
		SysrefcodeDO enterprise = sysrefcodeDao.getSysrefCodesByCode("CS_PARTICIPANT_TYPE", "002");


		//业主
		SysrefcodeDO owner = sysrefcodeDao.getSysrefCodesByCode("CS_CLIENT_CLIENT_TYPE", "001");
		//租户
		SysrefcodeDO lessee = sysrefcodeDao.getSysrefCodesByCode("CS_CLIENT_CLIENT_TYPE", "002");
		//业主家庭成员
		SysrefcodeDO ownerMember = sysrefcodeDao.getSysrefCodesByCode("CS_CLIENT_CLIENT_TYPE", "003");
		//租户家庭成员
		SysrefcodeDO lesseeMember = sysrefcodeDao.getSysrefCodesByCode("CS_CLIENT_CLIENT_TYPE", "004");

		StringBuffer tmp;
		StringBuffer target = new StringBuffer();//执行sql
		SQLSource src = new SQLSource();

		StringBuffer si = src.getSI();
		StringBuffer se = src.getSE();
		final StringBuffer siTail = new StringBuffer("");
		final StringBuffer seTail = new StringBuffer(" WHERE 1=1");

		class AddTail{
			public void addTail (StringBuffer tmp) {
				siTail.append(tmp);
				seTail.append(tmp);
			}
		}
		AddTail addTail = new AddTail();

	 	if(vo.getHouseNumber()!=null) {
	 		tmp = new StringBuffer(" AND HM_HOUSE.HOUSE_NUMBER LIKE '").append(vo.getHouseNumber()).append("'");
	 		addTail.addTail(tmp);
	 	}
	 	//indate has L
	 	if(vo.getBeginInDate()!=null) {
	 		tmp = new StringBuffer(" AND CS_RESIDE_IN_HIS.IN_DATE >= '").append(vo.getBeginInDate()).append("'");
	 		addTail.addTail(tmp);
	 	}
	 	//indate has R
	 	if(vo.getEndInDate()!=null) {
	 		tmp = new StringBuffer(" AND CS_RESIDE_IN_HIS.IN_DATE <= '").append(vo.getEndInDate()).append("'");
	 		addTail.addTail(tmp);
	 	}
	 	//outdate has L
	 	if(vo.getBeginOutDate()!=null) {
	 		tmp = new StringBuffer(" AND CS_RESIDE_IN_HIS.OUT_DATE >= '").append(vo.getBeginOutDate()).append("'");
	 		addTail.addTail(tmp);
	 	}
	 	//outdate has R
	 	if(vo.getEndOutDate()!=null) {
	 		tmp = new StringBuffer(" AND CS_RESIDE_IN_HIS.OUT_DATE <= '").append(vo.getEndOutDate()).append("'");
	 		addTail.addTail(tmp);
	 	}

//	 	2
		//name,phone
		//name has
		if (vo.getCustomerName() != null) {
			tmp = new StringBuffer(" AND CS_PARTICIPANT.PARTICIPANT_NAME LIKE '%").append(vo.getCustomerName()).append("%'");
			addTail.addTail(tmp);
		}
		//PHONE has
		if (vo.getTelephone() != null) {
			tmp = new StringBuffer(" AND CS_PARTICIPANT.TELEPHONE = '").append(vo.getTelephone()).append("'");
			addTail.addTail(tmp);
		}


	//3 {4}
	//CLIENTTYPE O,L,OM,LM
	//O
	if (owner.getValue().equals(vo.getCustomerType())) {
		tmp = new StringBuffer(" AND SYS_REF_CODE.SYS_REF_VALUE = '001'");
		addTail.addTail(tmp);

	//L
	} else if(lessee.getValue().equals(vo.getCustomerType())) {
		tmp = new StringBuffer(" AND SYS_REF_CODE.SYS_REF_VALUE = '002'");
		addTail.addTail(tmp);
	//OM
	} else if(ownerMember.getValue().equals(vo.getCustomerType())) {
		tmp = new StringBuffer(" AND SYS_REF_CODE.SYS_REF_VALUE = '003'");
		addTail.addTail(tmp);

	//LM
	} else if(lesseeMember.getValue().equals(vo.getCustomerType())) {
		tmp = new StringBuffer(" AND SYS_REF_CODE.SYS_REF_VALUE = '004'");
		addTail.addTail(tmp);
	//ALL
	} else {
	}

	//	4 PT
	//individual
	if (individual.getValue().equals(vo.getParticipantType())) {
		//nativeplace
		if (vo.getNativePlace() != null ) {
			tmp = new StringBuffer(" AND CS_INDIVIDUAL.HOMEPLACE like '%").append(vo.getNativePlace()).append("%'");
			si.append(tmp);
		}
		//interest
		if (vo.getBeInterestingIn() != null ) {
			tmp = new StringBuffer(" AND CS_INDIVIDUAL.INTEREST like '%").append(vo.getBeInterestingIn()).append("%'");
			si.append(tmp);
		}
		//profession
		if (vo.getProfession() != null ) {
			tmp = new StringBuffer(" AND CS_INDIVIDUAL.PROFESSION like '%").append(vo.getProfession()).append("%'");
			si.append(tmp);
		}

		target = si.append(siTail) ;
	} else if(enterprise.getValue().equals(vo.getParticipantType())){

		target = se.append(seTail);
	} else {
		target = new StringBuffer("SELECT * FROM (")
		.append(si).append(siTail)
		.append(" UNION ALL ")
		.append(se).append(seTail)
		.append(") TOGETHER");
	}
	    String orderString = wrapOrderString(vo.getOrderString());
		target.append(" ORDER BY "+orderString);
		System.out.println("Query:"+target.toString());
		return this.pagedSqlQueryByPageNo(target.toString(), pageNO, pageSize);
	}

	 //按照查询条件分情况讨论
	 class SQLSource{

			SysrefcodeDO individual = sysrefcodeDao.getSysrefCodesByCode("CS_PARTICIPANT_TYPE", "001");
			SysrefcodeDO enterprise = sysrefcodeDao.getSysrefCodesByCode("CS_PARTICIPANT_TYPE", "002");



			//查询个人
			private StringBuffer si = new StringBuffer(
			"SELECT PARTICIPANT_NAME,HOUSE_NUMBER,")
			.append("IF(SYS_REF_CODE.SYS_REF_VALUE='001' OR SYS_REF_CODE.SYS_REF_VALUE= '002',CONCAT(SYS_REF_CODE.SYS_REF_NAME,")
			.append("'(").append(individual.getName()).append(")'),SYS_REF_CODE.SYS_REF_NAME),")
			.append(" IN_DATE, OUT_DATE,")
			.append(" ID_TYPE_NAME,ID_NO,TELEPHONE,")
			.append(" INDIVIDUAL_ID,HM_HOUSE.HOUSE_ID,STOREY_NUMBER,SYS_REF_NAME")
			.append(" FROM CS_RESIDE_IN_HIS")
			.append(" INNER JOIN HM_HOUSE ON CS_RESIDE_IN_HIS.HOUSE_ID = HM_HOUSE.HOUSE_ID  AND (HM_HOUSE.IS_VIRTUAL='0')")
			.append(" INNER JOIN CS_FLAT ON CS_FLAT.FLAT_ID = HM_HOUSE.HOUSE_ID")
			.append(" INNER JOIN CS_CLIENT ON CS_RESIDE_IN_HIS.CLIENT_ID = CS_CLIENT.CLIENT_ID")
			.append(" INNER JOIN SYS_REF_CODE ON CS_CLIENT.SYS_REF_ID = SYS_REF_CODE.SYS_REF_ID")
			.append(" INNER JOIN CS_PARTICIPANT_ROLE ON CS_CLIENT.CLIENT_ID = CS_PARTICIPANT_ROLE.PARTICIPANT_ROLE_ID")
			.append(" INNER JOIN CS_PARTICIPANT ON CS_PARTICIPANT.PARTICIPANT_ID = CS_PARTICIPANT_ROLE.PARTICIPANT_ID")
			.append(" INNER JOIN CS_INDIVIDUAL ON CS_PARTICIPANT.PARTICIPANT_ID = CS_INDIVIDUAL.INDIVIDUAL_ID")
			.append(" LEFT JOIN CS_ID_TYPE ON CS_INDIVIDUAL.ID_TYPE_ID = CS_ID_TYPE.ID_TYPE_ID");



			//查询企业
			private StringBuffer se = new StringBuffer(
					"SELECT PARTICIPANT_NAME,")
			.append("HOUSE_NUMBER,")
			.append("IF(SYS_REF_CODE.SYS_REF_VALUE='001' OR SYS_REF_CODE.SYS_REF_VALUE= '002',CONCAT(SYS_REF_CODE.SYS_REF_NAME,")
			.append("'(").append(enterprise.getName()).append(")'),SYS_REF_CODE.SYS_REF_NAME),")
			.append(" IN_DATE, OUT_DATE,")
			.append(" ID_TYPE_NAME,ID_NO,")
			.append(" TELEPHONE, ORGANIZATION_ID,")
			.append(" HM_HOUSE.HOUSE_ID,STOREY_NUMBER,SYS_REF_NAME")
			.append(" FROM CS_RESIDE_IN_HIS")
			.append(" INNER JOIN HM_HOUSE ON CS_RESIDE_IN_HIS.HOUSE_ID = HM_HOUSE.HOUSE_ID AND (HM_HOUSE.IS_VIRTUAL='0')")
			.append(" INNER JOIN CS_FLAT ON CS_FLAT.FLAT_ID = HM_HOUSE.HOUSE_ID")
			.append(" INNER JOIN CS_CLIENT ON CS_RESIDE_IN_HIS.CLIENT_ID = CS_CLIENT.CLIENT_ID")
			.append(" INNER JOIN SYS_REF_CODE ON CS_CLIENT.SYS_REF_ID = SYS_REF_CODE.SYS_REF_ID")
			.append(" INNER JOIN CS_PARTICIPANT_ROLE ON CS_CLIENT.CLIENT_ID = CS_PARTICIPANT_ROLE.PARTICIPANT_ROLE_ID")
			.append(" INNER JOIN CS_PARTICIPANT ON CS_PARTICIPANT.PARTICIPANT_ID = CS_PARTICIPANT_ROLE.PARTICIPANT_ID")
			.append(" INNER JOIN CS_ORGANIZATION ON CS_PARTICIPANT.PARTICIPANT_ID = CS_ORGANIZATION.ORGANIZATION_ID")
			.append(" INNER JOIN (SELECT PARTICIPANT_ID1 , PARTICIPANT_ID2 ,ID_TYPE_NAME,ID_NO FROM (SELECT  DISTINCT * FROM (")
			.append(" SELECT  PARTICIPANT_ID1 , PARTICIPANT_ID2  , CS_RELATION_TYPE.RELATION_TYPE_ID")
			.append(" FROM")
			.append(" CS_PARTICIPANT_RELATION INNER JOIN CS_RELATION_TYPE ON CS_PARTICIPANT_RELATION.RELATION_TYPE_ID = CS_RELATION_TYPE.RELATION_TYPE_ID WHERE RELATION_TYPE_CODE=6")
			.append(" UNION ALL")
			.append(" SELECT")
			.append(" PARTICIPANT_ID2 , PARTICIPANT_ID1 ,CS_RELATION_TYPE.RELATION_TYPE_ID")
			.append(" FROM")
			.append(" CS_PARTICIPANT_RELATION INNER JOIN CS_RELATION_TYPE ON CS_PARTICIPANT_RELATION.RELATION_TYPE_ID = CS_RELATION_TYPE.RELATION_TYPE_ID WHERE RELATION_TYPE_CODE=6")
			.append(" ) RELATION) RELATION  INNER JOIN CS_INDIVIDUAL ON CS_INDIVIDUAL.INDIVIDUAL_ID = RELATION.PARTICIPANT_ID1")
			.append(" LEFT JOIN CS_ID_TYPE ON CS_INDIVIDUAL.ID_TYPE_ID = CS_ID_TYPE.ID_TYPE_ID")
			.append(" ")
			.append(" ) LEGAL ON LEGAL.PARTICIPANT_ID2 = CS_ORGANIZATION.ORGANIZATION_ID");

			/**
			 * 查询个人业主和租户,家庭成员
			 * @return
			 */
			public StringBuffer getSI() {
				return si
				.append(" WHERE (SYS_REF_CODE.SYS_REF_VALUE = '001' OR SYS_REF_CODE.SYS_REF_VALUE = '002' OR SYS_REF_CODE.SYS_REF_VALUE = '003' OR SYS_REF_CODE.SYS_REF_VALUE = '004')");
			}
			/**
			 * 查询企业业主租户
			 * @return
			 */
			public StringBuffer getSE() {
				return se;
			}

		}

	 /***
	  * Function: 封装排序条件
	  * @param orderStr
	  * @return
	  * Create author:燕大为
	  * Create on:2008-7-4
	  * Edit author:
	  * Edit on:
	  * Why:
	  */
	 private String wrapOrderString(String orderStr)
	 {
		StringBuffer orderString = new StringBuffer();
		String order = null;
		if(orderStr.indexOf("desc") != -1)
			order = "DESC";
		else
			order = "ASC";
		if(BlankUtil.isBlank(orderStr))
		{
			orderString.append(" STOREY_NUMBER ").append(order).append(", HOUSE_NUMBER ").append(order);
		}
		else
		{
			if(orderStr.indexOf("houseNumber") != -1)
			{
				orderString.append(" STOREY_NUMBER ").append(order).append(", HOUSE_NUMBER ").append(order);
			}
			else if(orderStr.indexOf("customerName")!= -1)
			{
				orderString.append(" PARTICIPANT_NAME ").append(order);
			}
			else if(orderStr.indexOf("customerType")!= -1)
			{
				orderString.append(" SYS_REF_NAME ").append(order);
			}
			else if(orderStr.indexOf("inDate")!= -1)
			{
				orderString.append(" IN_DATE ").append(order);
			}
			else if(orderStr.indexOf("outDate")!= -1)
			{
				orderString.append(" OUT_DATE ").append(order);
			}
		}
		return orderString.toString();
	 }




}
